﻿using System;
using System.Collections.Generic;
using System.Data;
using Model;



public class ReferenceDataDirectService
{
   private ConnectionBase sgbdr;
   public ReferenceDataDirectService()
    {
      sgbdr= new ConnectionBase();
    }
   
      

       public DataTable GetModelByTypeEquipmentId(int typeId)
       {
           string query = "SELECT * FROM model WHERE ModelID in " +
            "(SELECT MODELID FROM [IN] WHERE TYPEEQUIPEMENTID = "+ typeId+") ORDER BY MODEL";

           return  sgbdr.consultSQL(query).Tables[0];
       }
       public DataTable GetProblemByTypeEquipmentId(int typeId)
       {
           string query = "SELECT * FROM GROUPEPROBLEM WHERE TYPEEQUIPEMENTID = " + typeId + " ORDER BY GROUPEPROBLEM";
           return sgbdr.consultSQL(query).Tables[0];
       }
       public int GetVGAId()
       {
           string query1 = "select * from typeequipment where typeequipment like 'VGA'";
           if (sgbdr.consultSQL(query1).Tables[0].Rows.Count > 0)
               return Int32.Parse(sgbdr.consultSQL(query1).Tables[0].Rows[0]["typeequipementid"].ToString());
           return -1;

       }
       public DataTable GetEquipmentSystemEquipmentType(int systemId)
       {
           // typeequipment = 1  : system ,we hide it from user 
           string sql = " select  * from TYPEEQUIPMENT where isSparepart = 0 and typeequipementID  " +
                        " in (select typeequipementid from [have] where typeequipementid  != 1 and systemid = " +
                        systemId +
                        ")";
           return sgbdr.consultSQL(sql).Tables[0];
       }

       
       public DataTable GetSparepartSystemEquipmentType(int systemId)
       {
           string sql = " select  * from TYPEEQUIPMENT where isSparepart = 1 and typeequipementID  " +
                        " in (select typeequipementid from [have] where typeequipementid  != 1 and systemid = " +
                        systemId +
                        ")";
           return sgbdr.consultSQL(sql).Tables[0];
       }
       public DataTable GetNumberOfMonthsToDoPreventiveMantenance()
       {
           string sql = "select Distinct(NUMBEROFMONTHS) from PreventiveMaintenance ";
           return sgbdr.consultSQL(sql).Tables[0];
       }

    public int GetRamId()
       {
           string query1 = "select * from typeequipment where typeequipment like 'ram'";
           if (sgbdr.consultSQL(query1).Tables[0].Rows.Count > 0)
               return Int32.Parse(sgbdr.consultSQL(query1).Tables[0].Rows[0]["typeequipementid"].ToString());
           return -1;

       }
       public int GetSoundCardId()
       {
           string query1 = "select * from typeequipment where typeequipment like 'Sound Card'";
           if (sgbdr.consultSQL(query1).Tables[0].Rows.Count > 0)
               return Int32.Parse(sgbdr.consultSQL(query1).Tables[0].Rows[0]["typeequipementid"].ToString());
           return -1;
       }
       public int GetCPUId()
       {
           string query1 = "select * from typeequipment where typeequipment like 'cpu'";
           if (sgbdr.consultSQL(query1).Tables[0].Rows.Count > 0)
               return Int32.Parse(sgbdr.consultSQL(query1).Tables[0].Rows[0]["typeequipementid"].ToString());
           return -1;

       }
       public int GetNetworkCardId()
       {
           string query1 = "select * from typeequipment where typeequipment like 'Network Card'";
           if (sgbdr.consultSQL(query1).Tables[0].Rows.Count > 0)
               return Int32.Parse(sgbdr.consultSQL(query1).Tables[0].Rows[0]["typeequipementid"].ToString());
           return -1;

       }
       public int GetHardiskId()
       {
           string query1 = "select * from typeequipment where typeequipment like 'hardDrive'";
           if (sgbdr.consultSQL(query1).Tables[0].Rows.Count > 0)
               return Int32.Parse(sgbdr.consultSQL(query1).Tables[0].Rows[0]["typeequipementid"].ToString());
           return -1;

       }
       public int GetComputerId()
       {
           string query1 = "select * from typeequipment where typeequipment like 'computer'";
           if (sgbdr.consultSQL(query1).Tables[0].Rows.Count > 0)
               return Int32.Parse(sgbdr.consultSQL(query1).Tables[0].Rows[0]["typeequipementid"].ToString());
           return -1;

       }
       public int GetDefaultSystemId()
       {
           string query1 = "select * from [system] where system like 'other'";
           if (sgbdr.consultSQL(query1).Tables[0].Rows.Count > 0)
               return Int32.Parse(sgbdr.consultSQL(query1).Tables[0].Rows[0]["systemid"].ToString());
           return -1;

       }
       public int GetComputerCodeNumberLastId()
       {
           string query1 = "select top 1* from infosComputer";
           if (sgbdr.consultSQL(query1).Tables[0].Rows.Count > 0)
               return Int32.Parse(sgbdr.consultSQL(query1).Tables[0].Rows[0]["codenumber"].ToString());
           return 0;
       }
        public DataTable GetVGAModel()
        {
            return GetModelByTypeEquipmentId(GetVGAId());
        }
        public DataTable GetRamModel()
        {
            return GetModelByTypeEquipmentId(GetRamId());
        }
        public DataTable GetSoundCardModel()
        {
            return GetModelByTypeEquipmentId(GetSoundCardId());
        }
        public DataTable GetCPUModel()
        {
            return GetModelByTypeEquipmentId(GetCPUId());
        }
        public DataTable GetNetworkCardModel()
        {
            return GetModelByTypeEquipmentId(GetNetworkCardId());
        }
        public DataTable GetHardiskModel()
        {
            return GetModelByTypeEquipmentId(GetHardiskId());
        }
        public DataTable GetComputerSpareparts(string CodeNumber)
        {
            string sql = "select spare.modelid,info.* from sparepartpurchaseInformation as info, sparepart as spare "+
                        " where info.informationid  = spare.informationid and "+
                        " spare.InformationId in "+
                        " ( select distinct(sparepart.informationId) from sparepart,compose "+
                        " where (sparepart.Sparepartid  = [compose].SparepartiD "+
                        " and [compose].codeNumber = '"+CodeNumber +"')"+
                        ")";


            DataTable ds = sgbdr.consultSQL(sql).Tables[0];
            return ds;
        }
        public IList<SparepartPurchaseInfo> GetComputerSparepartsList(string CodeNumber)
        {
            DataTable dataTable = (GetComputerSpareparts(CodeNumber));
            IList<SparepartPurchaseInfo> listItem = new List<SparepartPurchaseInfo>();

            foreach (DataRow row in dataTable.Rows)
            {
                SparepartPurchaseInfo equip = new SparepartPurchaseInfo();
                equip.Capacity = Int32.Parse(row["capacity"].ToString());
                equip.CompanyId = Int32.Parse(row["COMPANYID"].ToString());
                equip.ModelIdTmp = Int32.Parse(row["modelId"].ToString());
                equip.SparepartPurchaseInfoId = Int32.Parse(row["informationId"].ToString());
                equip.HaveId = Int32.Parse(row["haveId"].ToString());
                listItem.Add(equip);
            }
            return listItem;
        }
      
    
}
